/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2 
PILOT: SCRAP
DATE: 9/7/2023

PROJECT DESCRIPTION: 
Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		


DETAILS (This Syntax): 
Rerun order pull using LU file for CARE- UDS look up codes do not 
include procedure codes for FIT or FOBT; limit orders to known order dates(omits surgical history 
and hmt dates)

Input Files:
scrap.CRC_patient_covariates

Output Files: 
scrap.CRC_order_dates_new
scrap.CRC_screen_dates_new


/*Copy and paste into your program*/
/*file your patient ids are in*/
%let patients=scrap.CRC_patient_covariates;
/*name of patient id variable to link to Clarity [patient identifier]*/
%let id=raw_patid;
/*name of file you want your look up codes stored in*/
%let lu_file=care.CRC_SCREENS_LU;
/*name of file you want your output order dates stored in*/
%let out_orders=scrap.CRC_order_dates_new;
/*name of file you want your output screen dates stored in*/
%let out_screen=scrap.CRC_screen_dates_new;
/*study period start-10 years;*/
%let start_date='01JUL2006'd;
/*study period end*/
%let end_date='29FEB2020'd;


*****************************************************/;
options compress=yes reuse=yes;
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;
libname care 'E:\sasroot\BRIDGE C2\Care Gaps';
libname scrap 'E:\sasroot\BRIDGE C2\SCRAP';


proc sql; create table RD.jpo_crc_screens_lu as select * from &lu_file; quit;
proc sql;create table patients as select distinct raw_patid from &patients;quit;
proc sql; create table RD.jpo_crc_patients as select distinct raw_patid from patients; quit;


*screening orders and results from order procedure;
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table crc_orders as 
select * from connection to mycon (

SELECT distinct
	den.&id
    ,op.[ORDERING DATE]
	,op.[result time]
	,op.[procedure identifier]
	,[order status]
	,ppc.screen
	,op.[pending order yes/no]
	,op.[order procedure identifier]
FROM
    research_dev.dbo.jpo_crc_patients  AS den
		inner join Clarity.dbo.[ORDER PROCEDURE] as op
			on den.&id = op.[patient identifier] 
        inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
      ON op.[procedure identifier] = ppc.[grouper records numeric identifier]);
quit;

data CRC_orders ;
set CRC_orders;
length source $20;
Source='Order proc';
format order_date screen_date 9.;
order_date=datepart([ORDERING DATE]);
screen_date=datepart([result time]);
where [order status]^=4;
if order_date<&start_date then delete;
if order_date>&end_date then delete;
run;

data CRC_screen_procs;
set crc_orders;
where [result time]^=.;
if screen_date<&start_date then delete;
if screen_date<&end_date then delete;
run;


proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_components as select *
from connection to mycon (
select distinct pt.&id,  
	r.[component identifier], 	
	zr.[result status], 
	zo.[order status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ORDERING DATE], 
	r.[result time], 
	o.[patient encounter contact serial number identifier], 
	r.[component lnc identifier],
	cc.[default lnc identifier],
	ppc.screen
from  CLARITY.dbo.[ORDER RESULTS] r
inner join  CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_crc_patients  pt on pt.&id=o.[patient identifier]
left join  CLARITY.dbo.[LAB STATUS] zr on zr.[lab status]=r.[lab status]
left join  CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join  CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
left join clarity.dbo.[component] cc on cc.[component identifier]=r.[component identifier]
inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
on ppc.[component identifier]=r.[component identifier]
where ppc.component_id is not null);

data clarity_LAB_components;
set  clarity_LAB_components; 
length source $20;
format order_date screen_date date9.;
Source='Lab Component';
order_date=datepart([ORDERING DATE]); 
screen_date=datepart([result time]);
/*if screen_date<&start_date then delete;*/
if screen_date>&end_date then delete;
run;

proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_LNC_cc as select *
from connection to mycon (
select distinct pt.&id,  
	r.[component identifier], 	
	zr.[result status], 
	zo.[order status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ORDERING DATE], 
	r.[result time], 
	o.[patient encounter contact serial number identifier], 
	cc.[default lnc identifier],
	r.[component lnc identifier],
	ppc.screen
from  CLARITY.dbo.[ORDER RESULTS] r
inner join  CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_crc_patients  pt on pt.&id=o.[patient identifier]
left join  CLARITY.dbo.[LAB_STATUS] zr on zr.[lab status]=r.[lab status]
left join  CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join  CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
left join clarity.dbo.[component] cc on cc.[component identifier]=r.[component identifier]
inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
on ppc.LNC_id=cc.[default lnc identifier] 
where ppc.lnc_id is not null);

data clarity_LAB_LNC_cc;
set  clarity_LAB_LNC_cc; 
length source $20;
format order_date screen_date date9.;
Source='Lab CC LNC';
order_date=datepart([ORDERING DATE]); 
screen_date=datepart([result time]);
/*if screen_date<&start_date then delete;*/
if screen_date>&end_date then delete;
run;

proc sql;
create table lab_LN_cc as
select * from clarity_LAB_LNC_cc
where [order procedure identifier] ^in(select distinct [order procedure identifier] from 
	clarity_LAB_components);
quit;

data lab_results;
set clarity_LAB_components lab_LN_cc;
run;

proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_LNC_or as select *
from connection to mycon (
select distinct pt.&id,  
	r.[component identifier], 	
	zr.title as result_status, 
	zo.title as order_status,
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ORDERING DATE], 
	r.[result time], 
	o.[patient encounter contact serial number identifier], 
	r.[component lnc identifier],
	ppc.screen
from  CLARITY.dbo.[ORDER RESULTS] r
inner join  CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_crc_patients  pt on pt.&id=o.[patient identifier]
left join  CLARITY.dbo.[LAB STATUS] zr on zr.[lab status]=r.[lab status]
left join  CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join  CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
inner join research_dev.dbo.jpo_crc_screens_lu AS ppc
on ppc.LNC_id=r.[component lnc identifier] 
where ppc.lnc_id is not null);


data clarity_LAB_LNC_or;
set  clarity_LAB_LNC_or; 
length source $20;
format order_date screen_date date9.;
Source='Lab OR LNC';
order_date=datepart([ORDERING DATE]); 
screen_date=datepart([result time]);
/*if order_date<&start_date then delete;*/
if screen_date>&end_date then delete;
run;

proc sql;
create table lab_LN_OR as
select * from clarity_LAB_LNC_cc
where [order procedure identifier] ^in(select distinct [order procedure identifier] from 
	lab_results);
quit;

/*drop records already included in crc_screen procs*/
proc sql;
create table new_results as
select * from lab_results
where [order procedure identifier]=. or [order procedure identifier] ^in
(select distinct [order procedure identifier] from crc_screen_procs);

/*combine orders*/
data orders;
set crc_orders new_results;
if order_date<&start_date then delete;/*drops lab results without order dates*/
run;

*deduplicate;
proc sql;
create table orders2  as
select &id,
screen,
order_date,
screen_date,
[order procedure identifier],
count([order procedure identifier]) as records,
count(distinct source) as sources
from orders where order_date^=.
group by &id,
screen,
order_date,
screen_date,
[order procedure identifier];

data &out_orders;
set orders2;
format order_date screen_date date9.;
if screen_date-order_date< -30 then do;
	screen_date=.;
end;
run;

/*Screens from [HEALTH MAINTENANCE HISTORY]*/
proc sql;
connect to odbc as mycon
(required = "dsn=OCHIN_HCN_FENWAY;Trusted_Connection=yes");
create table HMT_hx as select *
from connection to mycon (
SELECT
	DN.&ID
	,HMH.[HEALTH MAINTENANCE TOPIC IDENTIFIER]
	,HMH.[HEALTH MAINTENANCE HISTORY DATE]
FROM
	Clarity.dbo.[HEALTH MAINTENANCE HISTORY] as HMH
		inner join research_dev.dbo.jpo_crc_patients as DN
			on DN.&ID = HMH.[patient identifier]
WHERE
	HMH.[HEALTH MAINTENANCE TOPIC IDENTIFIER] IN (30,31,32,75)
	and HMH.[HEALTH MAINTENANCE TYPE] = 1 /*--HMO_STATUS_ID of 'Done'*/
	and HMH.[HEALTH MAINTENANCE HISTORY DATE] is not null);

data HMT_HX;
set HMT_HX;
length source $20 screen $25;

format screen_date date9.;
screen_date=datepart([HEALTH MAINTENANCE HISTORY DATE]);
Source='HMT HX';
if screen_date<&start_date then delete;
if screen_date>&end_date then delete;
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=30 then screen='FOBT';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=75 then screen='FIT';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=31 then screen='Colonoscopy';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER]=32 then screen='Flexible Sigmoidoscopy';
run;

/*pull from surgical history*/
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table CRC_shx as 
select * from connection to mycon (
     SELECT den.*	
  	,shx.[contact date] 
	,shx.[surgical history start date]
     ,shx.[SURGICAL HISTORY DATE] 
     ,cpc.screen
    FROM research_dev.dbo.jpo_crc_patients  AS den
     INNER JOIN clarity.dbo.[SURGICAL HISTORY]        AS shx
      ON den.&ID = shx.[patient identifier]
     INNER JOIN research_dev.dbo.jpo_crc_screens_lu AS cpc
      ON shx.[PROCEDURE IDENTIFIER] = cpc.[grouper records numeric identifier]
	where cpc.compiled_context='EAP')
	;      
quit;

data CRC_SHX2 ;
	set CRC_SHX;
	length source $20;
	Source='Surgical Hx';
	month=0;
	day=0;
	format screen_date date9.;
	if [surgical history start date]^=. then
		do;
			screen_date=datepart([surgical history start date]);
			if screen_date>=&start_date and screen_date<=&end_date then 
				output CRC_SHX2;
		end;
	if [surgical history start date]=. then
		do;
			if [SURGICAL HISTORY DATE]^=' ' and [SURGICAL HISTORY DATE]^='NO REPORT' then
				do;
					if index([SURGICAL HISTORY DATE],'/')^=0 then
						do;
							month=input (scan([SURGICAL HISTORY DATE],1,'/'),best4.);
							day=input (scan([SURGICAL HISTORY DATE],2,'/'),best4.);
							year=input (scan([SURGICAL HISTORY DATE],3,'/'),best4.);
						end;
					if index([SURGICAL HISTORY DATE],'-')^=0 then
						do;
							month=input (scan([SURGICAL HISTORY DATE],1,'-'),best4.);
							day=input (scan([SURGICAL HISTORY DATE],2,'-'),best4.);
							year=input (scan([SURGICAL HISTORY DATE],3,'-'),best4.);
						end;
					if month>0 and month<13 and
						day>0 and day<32 and
						year>2005 and year<2022 then
							do;
								screen_date=mdy(month, day, year);
							end;
			if screen_date>=&start_date and screen_date<=&end_date then 
				output CRC_SHX2;
				end;
		end;
run;

/* combine screens to link dates to orders that do not have result dates*/
data new_results2;/*omits screens already linked to orders*/
set new_results;
where [order procedure identifier]=.;
run;
data screens;
set new_results crc_shx2 HMT_HX;
run;

/*deduplicate*/
proc sql;
create table &out_screen as
select &id,
screen,
screen_date,
sum(source='HMT HX') as HMT_HX_records,
sum(source in('Lab CC LNC','Lab Component')) as Lab_re_records,
sum(source='Surgical Hx') as Surg_hx_records
from screens
group by &id,
screen,
screen_date;

/*add next due*/
data &out_screen;
set &out_screen;
format next_due date9.;
if screen='Colonoscopy' then next_due2=intnx('month',screen_date,120,'s');
if screen in ('CT Colonography', 'Flexible Sigmoidoscopy') then
	next_due2=intnx('month',screen_date,60,'s');
if screen='FIT' then next_due2=intnx('month',screen_date,36,'s');
if screen='FOBT' then next_due2=intnx('month',screen_date,12,'s');
run;


/*check change in order_days*/
proc sql;
create table orders as
select n.*,
o.raw_patid=' ' as new_order 
from  scrap.crc_order_dates_new n
left join scrap.crc_order_dates o
on n.raw_patid=n.raw_patid and
n.screen=o.screen and
o.order_date=n.order_date;


/*Colonoscopies*/
data colonoscopy_screens;
set scrap.crc_screen_dates_new;
where screen='Colonoscopy' ;
run;

proc sort nodupkey data=orders out=colonoscopy;
by raw_patid descending order_date;
where screen='Colonoscopy';
run;

data colonoscopy;
set colonoscopy;
format next_order next_result date9.;
next_order=lag(order_date);
next_result=lag(screen_date);
by raw_patid;
if first.raw_patid then do;
	next_result=&end_date;
	next_order=&end_date;
end;
if next_result=. then next_result=&end_date;
run;

proc sql;
create table colonoscopy2 as
select o.*,
a.first_due_date,
a.observation_end,
s.screen_date as result_date format date9.
from colonoscopy o inner join scrap.crc_analysis a
on o.raw_patid=a.raw_patid and o.order_date
between a.first_due_date and a.observation_end
left join colonoscopy_screens s
on s.raw_patid=o.raw_patid and s.screen_date between o.order_date and 
a.observation_end
order by raw_patid, order_date, result_date;


/*combine screen_date from [ORDER PROCEDURE] and result_dates from unlinked screens*/
data colonoscopy2;
set colonoscopy2;
format completion_date date9.;
completion_date=screen_date;
if result_date^=. then do;
	if screen_date=. or screen_date<order_date then completion_date=result_date;
	if ^(screen_date=. or screen_date<order_date) and 
		result_date<screen_date then completion_date=result_date;
end;
run;


/*Flexible Sigmoidoscopy*/
data flexsig_screens;
set scrap.crc_screen_dates_new;
where screen='Flexible Sigmoidoscopy';
run;
proc sort nodupkey data=orders out=flexsig;
by raw_patid descending order_date;
where screen='Flexible Sigmoidoscopy';
run;

data flexsig;
set flexsig;
format next_order next_result date9.;
next_order=lag(order_date);
next_result=lag(screen_date);
by raw_patid;
if first.raw_patid then do;/*last order*/
/*wnat results between this order and study end to link to this order*/
	next_result=&end_date+10;
	next_order=&end_date;
end;
if next_result=. then next_result=&end_date;
run;

proc sql;
create table flexsig2 as
select o.*,
s.screen_date as result_date format date9.,
a.first_due_date,
a.observation_end,
s.screen_date as result_date format date9.
from flexsig o inner join scrap.crc_analysis a
on o.raw_patid=a.raw_patid and o.order_date
between a.first_due_date and a.observation_end
left join flexsig_screens s
on s.raw_patid=o.raw_patid and s.screen_date between o.order_date and 
o.next_order and s.screen_date<=a.observation_end
order by raw_patid, order_date, result_date;


/*combine screen_date from [ORDER PROCEDURE] and result_dates from unlinked screens*/
data flexsig2;
set flexsig2;
format completion_date date9.;
completion_date=screen_date;
if result_date^=. then do;
	if screen_date=. or screen_date<order_date then completion_date=result_date;
	if ^(screen_date=. or screen_date<order_date) and 
		result_date<screen_date then completion_date=result_date;
end;
run;


/*FIT Screens*/
data FIT_screens;
set scrap.crc_screen_dates_new;
where screen='FIT' ;
run;

proc sort nodupkey data=orders out=FIT;
by raw_patid descending order_date;
where screen='FIT';
run;

data FIT;
set FIT;
format next_order next_result date9.;
next_order=lag(order_date);
next_result=lag(screen_date);
by raw_patid;
if first.raw_patid then do;/*last order*/
/*wnat results between this order and study end to link to this order*/
	next_result=&end_date;
	next_order=&end_date;
end;
if next_result=. then next_result=&end_date;
run;

proc sql;
create table FIT2 as
select o.*,
s.screen_date as result_date format date9.
from FIT o left join FIT_screens s
on s.raw_patid=o.raw_patid and s.screen_date between o.order_date and 
o.next_order and s.screen_date<o.next_result
order by raw_patid, order_date, result_date;

/*combine screen_date from [ORDER PROCEDURE] and result_dates from unlinked screens*/
data FIT2;
set FIT2;
format completion_date date9.;
completion_date=screen_date;
if result_date^=. then do;
	if screen_date=. or screen_date<order_date then completion_date=result_date;
	if ^(screen_date=. or screen_date<order_date) and 
		result_date<screen_date then completion_date=result_date;
end;
run;


/*FOBT Screens*/
data FOBT_screens;
set scrap.crc_screen_dates_new;
where screen='FOBT' ;
run;

proc sort nodupkey data=orders out=FOBT;
by raw_patid descending order_date;
where screen='FOBT';
run;

data FOBT;
set FOBT;
format next_order next_result date9.;
next_order=lag(order_date);
next_result=lag(screen_date);
by raw_patid;
if first.raw_patid then do;/*last order*/
/*wnat results between this order and study end to link to this order*/
	next_result=&end_date;
	next_order=&end_date;
end;
if next_result=. then next_result=&end_date;
run;

proc sql;
create table FOBT2 as
select o.*,
s.screen_date as result_date format date9.
from FOBT o left join FOBT_screens s
on s.raw_patid=o.raw_patid and s.screen_date between o.order_date and 
o.next_order and s.screen_date<o.next_result
order by raw_patid, order_date, result_date;

/*combine screen_date from [ORDER PROCEDURE] and result_dates from unlinked screens*/
data FOBT2;
set FOBT2;
format completion_date date9.;
completion_date=screen_date;
if result_date^=. then do;
	if screen_date=. or screen_date<order_date then completion_date=result_date;
	if ^(screen_date=. or screen_date<order_date) and 
		result_date<screen_date then completion_date=result_date;
end;
run;


/*************************************************************
stack files and link to patient observation period
***************************************************************/
data orders2;
set colonoscopy2 flexsig2 FIT2 fobt2;
run;

/*orders in first yar after due*/
proc sql;
create table orders3 as
select a.raw_patid,
a.first_due_date,
a.observation_end,
a.one_year_due,
o.[order procedure identifier], 
o.screen,
o.order_date,
o.completion_date
from scrap.crc_analysis a inner join 
orders2 o
on a.raw_patid=o.raw_patid and 
o.order_date between a.first_due_date and a.one_year_due
order by raw_patid, order_date;

data resulted_orders;
set orders3;
where completion_date^=.;
completion_2=completion_date;
if completion_date<order_date then completion_2=order_date;
run;

proc sql;
create table orders4 as
select o.*,
r.screen as prior_screen,
r.completion_2 as prior_result format date9.,
r.order_date as prior_order_date format date9.
from orders3 o left join resulted_orders r
on o.raw_patid=r.raw_patid and r.completion_2<o.order_date;

data orders5;
set orders4;
where prior_screen=' ';
run;

/************************************************************
code new outcome variables, summarize by patient
documented_FITFOBT_1yr
documented_imaging_1yr
documented_order_1yr
completed_FITFOBT_1yr
completed_imaging_1_yr
***********************************************************/
data orders6;
set orders5;
imaging_order=(screen in('Colonoscopy','Flexible Sigmoidoscopy'));
completed_FITFOBT=(imaging_order=0 and completion_date^=. and 
	completion_date<=one_year_due);
completed_imaging=(imaging_order=1 and completion_date^=. and 
	completion_date<=one_year_due);
run;

proc sql;
create table order_summary as
select raw_patid,
1 as documented_order_1yr,
max(imaging_order=0) as documented_FITFOBT_1yr,
max(imaging_order=1) as documented_imaging_1yr,
max(completed_FITFOBT) as completed_FITFOBT_1yr,
max(completed_imaging) as completed_imaging_1yr
from orders6
group by raw_patid;

 proc sql;
create table analysis as
select a.*,
max(0,o.documented_order_1yr) as documented_order_1yr,
max(0,o.documented_FITFOBT_1yr) as documented_FITFOBT_1yr,
max(0,o.documented_imaging_1yr) as documented_imaging_1yr,
max(0,o.completed_FITFOBT_1yr) as completed_FITFOBT_1yr,
max(0,o.completed_imaging_1yr) as completed_imaging_1yr
from scrap.CRC_ANALYSIS a left join order_summary o
on o.raw_patid=a.raw_patid;

data SCRAP.CRC_analysis_09062023;
set analysis;
run;
